package com.apt.load;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.apt.common.AptException;
import com.apt.common.DBUtility;
import com.apt.dao.LoadUsersDao;
import com.apt.domain.UserMaster;

public class LoadUsers {

	public static void main(String[] args) {
		LoadUsers rf = new LoadUsers();

		String fileName = "CSE.xls";
		// String fileName = "Avg.xls";
		// String fileName = "temp.xls";

		Connection con = null;

		String dbuserid = "root";
		String dbpassword = "password";
		// String url = "jdbc:oracle:thin:@dbss9017.uhc.com:1521:encdv2";
		String url = "jdbc:mysql://127.0.0.1:3306/APTDB";
		// String driverName = "oracle.jdbc.driver.OracleDriver";
		String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver

		int roleId = 0;
		int institutionId = 0;

		String institutionName = "Asan Memorial College of Engineering & Technology";
		String departmentId = "CSE";
		String userPassword = "asan".concat(departmentId);
		String departmentName = null;
		String userId = null;
		String collegeUserId = null;
		String firstName = null;

		PreparedStatement getRoleIdPs = null;
		PreparedStatement getInstitutionIdPs = null;
		PreparedStatement getDepartmentNamePs = null;
		PreparedStatement insertUserPs = null;

		ResultSet getRoleIdRs = null;
		ResultSet getInstitutionIdRs = null;
		ResultSet getDepartmentNameRs = null;

		String getRoleIdQuery = "SELECT ROLE_ID FROM ROLE_MASTER WHERE NAME='STUDENT'";

		String getInstitutionIdQuery = "SELECT INSTITUTION_ID FROM INSTITUTION_MASTER WHERE INSTITUTION_NAME = ? ";

		String getDepartmentNameQuery = "SELECT DESCRIPTION FROM DEPARTMENT_MASTER WHERE NAME = ? ";

		String insertUserQuery = "INSERT INTO USER_MASTER (USER_ID, COLLEGE_USER_ID, FIRST_NAME,PASSWORD,ROLE_ID,INSTITUTION_ID,DEPARTMENT)VALUES("
				+ "?,?,?,?,?,?,? )";

		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url, dbuserid, dbpassword);
			Workbook wb = Workbook.getWorkbook(new File(fileName));
			Sheet s = wb.getSheet(0);

			getRoleIdPs = con.prepareStatement(getRoleIdQuery);
			getRoleIdRs = getRoleIdPs.executeQuery();

			if (getRoleIdRs.next()) {
				roleId = getRoleIdRs.getInt("ROLE_ID");
			} else {
				throw new AptException(
						"No role defined for students in ROLE_MASTER table");
			}

			getInstitutionIdPs = con.prepareStatement(getInstitutionIdQuery);
			getInstitutionIdPs.setString(1, institutionName);

			getInstitutionIdRs = getInstitutionIdPs.executeQuery();

			if (getInstitutionIdRs.next()) {
				institutionId = getInstitutionIdRs.getInt("INSTITUTION_ID");
			} else {
				throw new AptException(
						"Institution name not found in INSTITUTION_MASTER table");
			}

			getDepartmentNamePs = con.prepareStatement(getDepartmentNameQuery);
			getDepartmentNamePs.setString(1, departmentId);

			getDepartmentNameRs = getDepartmentNamePs.executeQuery();

			if (getDepartmentNameRs.next()) {
				departmentName = getDepartmentNameRs.getString("DESCRIPTION");
			} else {
				throw new AptException(
						"Department name not found in DEPARTMENT_MASTER table");
			}

			insertUserPs = con.prepareStatement(insertUserQuery);

			for (int i = 0; i < s.getRows(); i++) {

				collegeUserId = s.getCell(1, i).getContents();
				firstName = s.getCell(2, i).getContents();

				userId = departmentId
						+ collegeUserId.substring(collegeUserId.length() - 4);

				// System.out.println(userId);

				insertUserPs.setString(1, userId);
				insertUserPs.setString(2, collegeUserId);
				insertUserPs.setString(3, firstName);
				insertUserPs.setString(4, userPassword);
				insertUserPs.setInt(5, roleId);
				insertUserPs.setInt(6, institutionId);
				insertUserPs.setString(7, departmentName);
				insertUserPs.executeUpdate();

			}

		} catch (BiffException be) {
			be.printStackTrace();
		} catch (IOException ie) {
			ie.printStackTrace();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (ClassNotFoundException ce) {
			ce.printStackTrace();
		} catch (AptException ae) {
			// do nothing
		}

	}

	public void readExcel(File readFile, int roleId, String[] departmentDetails,
			int institutionId) throws AptException{
		try {
			// Workbook wb = Workbook.getWorkbook(new File(fileName));
			Workbook wb1 = Workbook.getWorkbook(readFile);
			Sheet s = wb1.getSheet(0);
			List<UserMaster> userList = new ArrayList<UserMaster>();
			String collegeUserId;
			String firstName;
			String userId;
			String password = "asan".concat(departmentDetails[1]);

			for (int i = 0; i < s.getRows(); i++) {
				collegeUserId = s.getCell(1, i).getContents();
				firstName = s.getCell(2, i).getContents();
				//System.out.println(collegeUserId);
				userId = departmentDetails[1].concat(collegeUserId
						.substring(collegeUserId.length() - 4));
				//System.out.println(userId);
				UserMaster um = new UserMaster(userId, collegeUserId,
						firstName, password, roleId, institutionId,
						departmentDetails[0]);
				userList.add(um);
			}
			new LoadUsersDao(DBUtility.getConnection()).insertUsers(userList);
			
		} catch (BiffException be) {
			be.printStackTrace();
			throw new AptException("error in reading excel file");
		} catch (IOException ie) {
			ie.printStackTrace();
			throw new AptException("error in reading input file");
		}catch(AptException ae){
			ae.printStackTrace();
		}
	}
}